

ALTER        FUNCTION fn_shipped_orders_for_forecast_report(
   
   @CUSTOMER_INPUT VARCHAR(100),
   @PRODUCT_INPUT VARCHAR(100)
   )
   
   
   RETURNS @FN_RESULTS TABLE( CUSTOMER VARCHAR(100), PRODUCT VARCHAR(100), SALES_PERSON VARCHAR(100), END_USER VARCHAR(100), SHIPMONTH INT, SHIPQUARTER INT, SHIPYEAR INT, QUANTITY INT, UNIT_PRICE FLOAT )
     
   AS
   BEGIN
   	DECLARE @OLD_PRICE FLOAT
 
 	DECLARE @MKTG_PART_NUMBER VARCHAR(100) 
  	
  	DECLARE @TEMP_CURSOR CURSOR 
 
 	
   	IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT = '0'
   	BEGIN
 
  			SET @TEMP_CURSOR = CURSOR FOR
 	  	    	SELECT CUST.C_NAME[CUSTOMER], P.PART_NUMBER[PRODUCT], SO.SO_SALES_PERSON[SALES_PERSON], 
                        CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID = -1 OR SO.SO_ODM_ID = 0 OR SO.SO_ODM_ID = '' ) THEN CUST.C_Name
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
			END AS END_USER, 
 			DATEPART( MONTH, CST.CST_SHIP_DATE )-1, DATEPART( QUARTER, CST.CST_SHIP_DATE )-1,  
 	  		DATEPART( YEAR, CST.CST_SHIP_DATE ),CSLIT.CSLIT_QTY_SHIPPED[QUANTITY], COOL.COOL_UNIT_COST[UNIT_PRICE]
 	  		FROM CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, 
 	  		CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P, SALES_ORDER SO, CUSTOMER CUST  
 	  		WHERE 
			CST.CST_STATUS = 'SHIPPED'
 	 		AND CST.CUSTOMERSHIPPING_ID = CSLIT.CUSTOMERSHIPPING_ID
 	  		AND CSLIT.LINEITEM_ID = COOL.LINEITEM_ID
 	  		AND COOL.PRODUCT_ID = P.PRODUCT_ID
 	  		AND COOL.SALESORDER_ID = SO.SALESORDER_ID
 	 		AND SO.CUSTOMER_ID = CUST.CUSTOMER_ID
 			and SO.SO_Is_Sample = 1
 			 
  	END	
   	ELSE IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT <> '0'
   	BEGIN
   		
 			SET @TEMP_CURSOR = CURSOR FOR
 	  	    	SELECT CUST.C_NAME[CUSTOMER], P.PART_NUMBER[PRODUCT], SO.SO_SALES_PERSON[SALES_PERSON], 
                        CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID = -1 OR SO.SO_ODM_ID = 0 OR SO.SO_ODM_ID = '' ) THEN CUST.C_Name
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
			END AS END_USER,  
                        DATEPART( MONTH, CST.CST_SHIP_DATE )-1, DATEPART( QUARTER, CST.CST_SHIP_DATE )-1,  
 	  		DATEPART( YEAR, CST.CST_SHIP_DATE ),CSLIT.CSLIT_QTY_SHIPPED[QUANTITY], COOL.COOL_UNIT_COST[UNIT_PRICE]
 	  		FROM CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, 
 	  		CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P, SALES_ORDER SO, CUSTOMER CUST  
 	  		WHERE   P.PRODUCT_ID = @PRODUCT_INPUT 		
 	    		AND CST.CST_STATUS = 'SHIPPED'
 	 		AND CST.CUSTOMERSHIPPING_ID = CSLIT.CUSTOMERSHIPPING_ID
 	  		AND CSLIT.LINEITEM_ID = COOL.LINEITEM_ID
 	  		AND COOL.PRODUCT_ID = P.PRODUCT_ID
 	  		AND COOL.SALESORDER_ID = SO.SALESORDER_ID
 	 		AND SO.CUSTOMER_ID = CUST.CUSTOMER_ID
 			and SO.SO_Is_Sample = 1
 		
  	END
   	ELSE IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT = '0'
   	BEGIN
   		
 			SET @TEMP_CURSOR = CURSOR FOR
 	  	    	SELECT CUST.C_NAME[CUSTOMER], P.PART_NUMBER[PRODUCT], SO.SO_SALES_PERSON[SALES_PERSON], 
                        CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID = -1 OR SO.SO_ODM_ID = 0 OR SO.SO_ODM_ID = '' ) THEN CUST.C_Name
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
			END AS END_USER, 
                        DATEPART( MONTH, CST.CST_SHIP_DATE )-1, DATEPART( QUARTER, CST.CST_SHIP_DATE )-1,  
 	  		DATEPART( YEAR, CST.CST_SHIP_DATE ),CSLIT.CSLIT_QTY_SHIPPED[QUANTITY], COOL.COOL_UNIT_COST[UNIT_PRICE]
 	  		FROM CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, 
 	  		CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P, SALES_ORDER SO, CUSTOMER CUST  
 	  		WHERE  CUST.CUSTOMER_ID = @CUSTOMER_INPUT
 	  		AND CST.CST_STATUS = 'SHIPPED'
 	 		AND CST.CUSTOMERSHIPPING_ID = CSLIT.CUSTOMERSHIPPING_ID
 	  		AND CSLIT.LINEITEM_ID = COOL.LINEITEM_ID
 	  		AND COOL.PRODUCT_ID = P.PRODUCT_ID
 	  		AND COOL.SALESORDER_ID = SO.SALESORDER_ID
 	 		AND SO.CUSTOMER_ID = CUST.CUSTOMER_ID
 			and SO.SO_Is_Sample = 1
 	END
   	ELSE IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT <> '0'
   	BEGIN
   		
 			SET @TEMP_CURSOR = CURSOR FOR
 	  	    	SELECT CUST.C_NAME[CUSTOMER], P.PART_NUMBER[PRODUCT], SO.SO_SALES_PERSON[SALES_PERSON], 
                        CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID = -1 OR SO.SO_ODM_ID = 0 OR SO.SO_ODM_ID = '' ) THEN CUST.C_Name
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
			END AS END_USER,  
                        DATEPART( MONTH, CST.CST_SHIP_DATE )-1, DATEPART( QUARTER, CST.CST_SHIP_DATE )-1,  
 	  		DATEPART( YEAR, CST.CST_SHIP_DATE ),CSLIT.CSLIT_QTY_SHIPPED[QUANTITY], COOL.COOL_UNIT_COST[UNIT_PRICE]
 	  		FROM CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, 
 	  		CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P, SALES_ORDER SO, CUSTOMER CUST  
 	  		WHERE  CUST.CUSTOMER_ID = @CUSTOMER_INPUT
 	  		AND P.PRODUCT_ID = @PRODUCT_INPUT 		
 	  		AND CST.CST_STATUS = 'SHIPPED'
 	 		AND CST.CUSTOMERSHIPPING_ID = CSLIT.CUSTOMERSHIPPING_ID
 	  		AND CSLIT.LINEITEM_ID = COOL.LINEITEM_ID
 	  		AND COOL.PRODUCT_ID = P.PRODUCT_ID
 	  		AND COOL.SALESORDER_ID = SO.SALESORDER_ID
 	 		AND SO.CUSTOMER_ID = CUST.CUSTOMER_ID
 			and SO.SO_Is_Sample = 1
 	END
  	
  
   	DECLARE @CUSTOMER VARCHAR(100)
   	DECLARE @PRODUCT VARCHAR(100)
 	DECLARE @SALES_PERSON VARCHAR(100)
 	DECLARE @END_USER VARCHAR(100)
   	DECLARE @MONTH INT
	DECLARE @QUARTER INT	
   	DECLARE @YEAR INT
   	DECLARE @QUANTITY INT
   	DECLARE @OLD_QUANTITY INT
   	DECLARE @UNIT_PRICE FLOAT
   	DECLARE @ROW_COUNT INT
   	
 	
 	  	OPEN @TEMP_CURSOR 
 	  		FETCH NEXT FROM @TEMP_CURSOR
 	  		INTO @CUSTOMER, @PRODUCT, @SALES_PERSON, @END_USER, @MONTH, @QUARTER, @YEAR, @QUANTITY, @UNIT_PRICE
 	  		
 	  		WHILE @@FETCH_STATUS = 0
 	  		BEGIN
 	  			INSERT INTO @FN_RESULTS VALUES ( @CUSTOMER, @PRODUCT, @SALES_PERSON, @END_USER, @MONTH, @QUARTER, @YEAR, @QUANTITY, @UNIT_PRICE)
 	  					
 	  			FETCH NEXT FROM @TEMP_CURSOR
 	  			INTO  @CUSTOMER, @PRODUCT, @SALES_PERSON, @END_USER, @MONTH, @QUARTER, @YEAR, @QUANTITY, @UNIT_PRICE
 	  		END
 
 		CLOSE @TEMP_CURSOR
 	  	DEALLOCATE @TEMP_CURSOR
 
   	 
   	RETURN
   END
  






